create proc dbo.p_AverageDayWaitStates(
		@BeginDate datetime,
		@EndDate datetime,
		@WaitType varchar(50),
		@DatabaseID bigint) as

select
	datepart(yyyy,a.[CreateDate]) 'iYear',
	datepart(MM,a.[CreateDate]) 'iMonth',
	datepart(dd,a.[CreateDate]) 'iDay',
	datepart(hh,a.[CreateDate]) 'iHour',
	sum(a.[Requests]) 'SumOfRequests', 
	sum(a.[Wait_Time]) 'SumOfWait_Time',  
	sum(a.[Signal_Wait_Time]) 'SumOfSignal_Wait_Time',   	
	count(distinct a.SessionID) 'CountOfSessions'
into
	#x
from
	vwaitstatehistory a
where
	a.[databaseid]=@DatabaseID
	and a.[wait_type]=@WaitType
	and a.[createdate] between @BeginDate and @EndDate
group by
	datepart(yyyy,a.[CreateDate]),
	datepart(MM,a.[CreateDate]),
	datepart(dd,a.[CreateDate]),
	datepart(hh,a.[CreateDate])

declare @iHourCount int
set @iHourCount=datediff(hour,@BeginDate,@EndDate)

declare @hours table(
		dtDate datetime,
		dAverageRequests decimal(18,6),
		dAverageWait_Time decimal(18,6),
		dAverageSignal_Wait_Time decimal(18,6))   

declare @iHour int,  		
	@dtCurDate datetime,
	@iTotalCount int,
	@dAverageRequests decimal(18,6),
	@dAverageWait_Time decimal(18,6),
	@dAverageSignal_Wait_Time decimal(18,6)

set @dtCurDate=@BeginDate
while @dtCurDate<@EndDate
begin
	select
		@dAverageRequests=convert(decimal(18,6),a.SumOfRequests)/a.CountOfSessions,
		@dAverageWait_Time=convert(decimal(18,6),a.SumOfWait_Time)/a.CountOfSessions,
		@dAverageSignal_Wait_Time=convert(decimal(18,6),a.SumOfSignal_Wait_Time)/a.CountOfSessions
	from
		#x a
	where
		a.iYear=datepart(year,@dtCurDate)
		and a.iMonth=datepart(month,@dtCurDate)
		and a.iDay=datepart(day,@dtCurDate)
		and a.iHour=datepart(hh,@dtCurDate)

	insert into @hours(dtDate,dAverageRequests,dAverageWait_Time,dAverageSignal_Wait_Time) values (
		@dtCurDate,
		isnull(@dAverageRequests,0),
		isnull(@dAverageWait_Time,0),
		isnull(@dAverageSignal_Wait_Time,0))

	set @dtCurDate=dateadd(hour,1,@dtCurDate)
end

select
	*
from
	@hours
order by
	dtDate

drop table #x

